﻿--CREATE SCHEMA investment;
DROP TABLE investment.Portfolio
CREATE TABLE investment.Portfolio
(
	Code		NVARCHAR(8) NOT NULL,
	Name		NVARCHAR(128) NOT NULL,
	Description	NVARCHAR(4000) NOT NULL DEFAULT '',
	CreatedDate	DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	LastCorrectionDateTime	DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	NextCorrectionDateTime	DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (Code)
);
SELECT * FROM investment.Portfolio;

INSERT INTO investment.Portfolio (Code, Name, Description) VALUES ('aaa', 'aaa', 'aaa portfolio');
INSERT INTO investment.Portfolio (Code, Name, Description) VALUES ('bbb', 'bbb', 'bbb portfolio');
INSERT INTO investment.Portfolio (Code, Name, Description) VALUES ('ccc', 'ccc', 'ccc portfolio');
INSERT INTO investment.Portfolio (Code, Name, Description) VALUES ('ddd', 'ddd', 'ddd portfolio');
INSERT INTO investment.Portfolio (Code, Name, Description) VALUES ('eee', 'eee', 'eee portfolio');

CREATE SCHEMA app;

CREATE TABLE app.OpenIdProvider
(
	Code	NVARCHAR(8) NOT NULL, 
	Name	NVARCHAR(128) NOT NULL,
	Url		NVARCHAR(2048) NOT NULL,
	PRIMARY KEY (Code)
);
SELECT * FROM app.OpenIdProvider;
INSERT INTO app.OpenIdProvider (Code, Name, Url) VALUES ('GOOG', 'Google', 'https://www.google.com/accounts/o8/id');
INSERT INTO app.OpenIdProvider (Code, Name, Url) VALUES ('YHOO', 'Yahoo!', 'http://yahoo.com');

response.Provider.Uri.DnsSafeHost
-- open.login.yahooapis.com
-- 123456789012345678901234567890

DROP TABLE app.OpenIdUser
CREATE TABLE app.OpenIdUser
(
	Id			BIGINT Identity(1,1) NOT NULL, 
	DNS			NVARCHAR(128) NOT NULL,
	Alias		NVARCHAR(128) NOT NULL,
	Email		NVARCHAR(128) NOT NULL,
	FullName	NVARCHAR(128) NOT NULL,
	PRIMARY KEY(Id)
);

CREATE UNIQUE NONCLUSTERED INDEX [IX_OpenIdUser_UniqueUser] 
ON [app].[OpenIdUser] ([DNS], [Alias]);

use Arbalest

CREATE TABLE [investment].[Client] (
    [Code]                   NVARCHAR (8)    NOT NULL,
    [Name]                   NVARCHAR (128)  NOT NULL,
    [Description]            NVARCHAR (4000) DEFAULT ('') NOT NULL,
    [CreatedDate]            DATETIME        DEFAULT (getdate()) NOT NULL,
    PRIMARY KEY CLUSTERED ([Code] ASC)
);


Use Arbalest


CREATE TABLE [investment].[Portfolio] (
    [Code]                   NVARCHAR (8)    NOT NULL,
    [Name]                   NVARCHAR (128)  NOT NULL,
    [Description]            NVARCHAR (4000) DEFAULT ('') NOT NULL,
    [ClientCode]             NVARCHAR(8)     NOT NULL,
    [CreatedDate]            DATETIME        DEFAULT (getdate()) NOT NULL,
    [LastCorrectionDateTime] DATETIME        DEFAULT (getdate()) NOT NULL,
    [NextCorrectionDateTime] DATETIME        DEFAULT (getdate()) NOT NULL,
    PRIMARY KEY CLUSTERED ([Code] ASC)
);




CREATE TABLE [investment].[Purchase] (
    [Id]                    BIGINT IDENTITY(1,1) NOT NULL, 
    [PortfolioCode]         NVARCHAR(8)     NOT NULL,
    [MIC]                   NVARCHAR (8)    NOT NULL,
    [Code]                  NVARCHAR (8)    NOT NULL,
    [Quantity]              INT             NOT NULL DEFAULT 0,
    [Price]                 DECIMAL(28, 6)  NOT NULL DEFAULT 0,
    [Date]                  DATE            NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

-- How to translate a exchange::stock_code


INSERT INTO [investment].[Client] (Client.Code, Client.Name) VALUES ('ZX', 'ZX');
INSERT INTO [investment].[Portfolio] (Portfolio.Code, Portfolio.Name, Portfolio.ClientCode) VALUES ('P01', 'P01', 'ZX');
INSERT INTO [investment].[Purchase] (Purchase.PortfolioCode, Purchase.MIC, Purchase.Code, Purchase.Quantity, Purchase.Price) VALUES ('P01', 'XNAS', 'AAPL', 100, 1);
INSERT INTO [investment].[Purchase] (Purchase.PortfolioCode, Purchase.MIC, Purchase.Code, Purchase.Quantity, Purchase.Price) VALUES ('P01', 'XNAS', 'MSFT', 100, 1);
INSERT INTO [investment].[Purchase] (Purchase.PortfolioCode, Purchase.MIC, Purchase.Code, Purchase.Quantity, Purchase.Price) VALUES ('P01', 'XNAS', 'GOOG', 100, 1);
INSERT INTO [investment].[Purchase] (Purchase.PortfolioCode, Purchase.MIC, Purchase.Code, Purchase.Quantity, Purchase.Price) VALUES ('P01', 'XNAS', 'CSCO', 100, 1);
INSERT INTO [investment].[Purchase] (Purchase.PortfolioCode, Purchase.MIC, Purchase.Code, Purchase.Quantity, Purchase.Price) VALUES ('P01', 'XNAS', 'DELL', 100, 1);


CREATE TABLE [investment].[MIC] (
    [Code]                  NVARCHAR (8)    NOT NULL,
    [Name]                  NVARCHAR (128)    NOT NULL,
    PRIMARY KEY (Code)
);

SELECT * FROM Arbalest.[investment].[MIC] 
Use Arbalest
-- North America
INSERT INTO [investment].[MIC] (Code, MIC.Name) VALUES ('XNYS', 'NEW YORK STOCK EXCHANGE, INC.');
INSERT INTO [investment].[MIC] (Code, MIC.Name) VALUES ('XNAS', 'NASDAQ - ALL MARKETS');
INSERT INTO [investment].[MIC] (Code, MIC.Name) VALUES ('ARCX', 'NYSE ARCA');
INSERT INTO [investment].[MIC] (Code, MIC.Name) VALUES ('XCNQ', 'CANADIAN NATIONAL STOCK EXCHANGE');
-- Europe
INSERT INTO [investment].[MIC] (Code, MIC.Name) VALUES ('XLON', 'LONDON STOCK EXCHANGE');
INSERT INTO [investment].[MIC] (Code, MIC.Name) VALUES ('XFRA', 'DEUTSCHE BOERSE AG');
INSERT INTO [investment].[MIC] (Code, MIC.Name) VALUES ('XMIL', 'BORSA ITALIANA S.P.A.');
INSERT INTO [investment].[MIC] (Code, MIC.Name) VALUES ('MISX', 'MOSCOW EXCHANGE');
-- Asia
INSERT INTO [investment].[MIC] (Code, MIC.Name) VALUES ('XBOM', 'BSE LTD');
INSERT INTO [investment].[MIC] (Code, MIC.Name) VALUES ('XNSE', 'NATIONAL STOCK EXCHANGE OF INDIA');
INSERT INTO [investment].[MIC] (Code, MIC.Name) VALUES ('XSHG', 'SHANGHAI STOCK EXCHANGE');
INSERT INTO [investment].[MIC] (Code, MIC.Name) VALUES ('XSHE', 'SHENZHEN STOCK EXCHANGE');
INSERT INTO [investment].[MIC] (Code, MIC.Name) VALUES ('XTAI', 'TAIWAN STOCK EXCHANGE');
INSERT INTO [investment].[MIC] (Code, MIC.Name) VALUES ('SGX', 'SINGAPORE EXCHANGE');




SELECT * from investment.MIC

CREATE TABLE [investment].[Stock]
(
	[MarketIdentifierCode]	NVARCHAR (8)    NOT NULL,
	[Code]                  NVARCHAR (8)    NOT NULL,
    [Name]                  NVARCHAR (128)    NOT NULL,
    PRIMARY KEY (MIC, Code, Name)
);

Use Arbalest
SELECT TOP 20 * from [investment].[Stock]
SELECT * from [investment].[Portfolio]

-- DELETE from [investment].[Stock]



SELECT COUNT(*) from [investment].[Stock]
SELECT * from [investment].[Portfolio]
SELECT * from [investment].[Purchase]

SELECT * FROM investment.Portfolio pf
INNER JOIN investment.Purchase pc
ON pf.Code = pc.PortfolioCode
--DELETE from [investment].[Stock]
SELECT DISTINCT TOP 20 * FROM investment.Stock WHERE Name LIKE 'yaho%'



--http://finance.yahoo.com/q?s=YHOO
--https://www.google.com/finance?q=YHOO
--http://investing.money.msn.com/investments/stock-price?Symbol=YHOO
--http://money.cnn.com/quote/quote.html?symb=YHOO

CREATE TABLE [investment].[Datasource]
(
	[Code]                  NVARCHAR (8)    NOT NULL,
	[Url]					NVARCHAR(2048)	NOT NULL
	PRIMARY KEY(Code)
);

INSERT INTO [investment].[Datasource] (Code, Url) VALUES ('YAHOO',		'http://finance.yahoo.com/q?s={0}');
INSERT INTO [investment].[Datasource] (Code, Url) VALUES ('GOOGLE',		'https://www.google.com/finance?q={0}');
INSERT INTO [investment].[Datasource] (Code, Url) VALUES ('MSNMONEY',	'http://investing.money.msn.com/investments/stock-price?Symbol={0}');
INSERT INTO [investment].[Datasource] (Code, Url) VALUES ('CNNMONEY',	'http://money.cnn.com/quote/quote.html?symb={0}');

DROP TABLE [investment].[DataItem]

CREATE TABLE [investment].[DataItem]
(
	[Code]                  NVARCHAR (8)		NOT NULL,
	[Name]					NVARCHAR (128)		NOT NULL,
	[Description]			NVARCHAR (256)		NOT NULL DEFAULT '',
	PRIMARY KEY(Code)
)

INSERT INTO [investment].[DataItem] (Code, Name) VALUES (


CREATE TABLE investment.SymbolMap 
(
	[MarketIdentifierCode]	NVARCHAR (8)    NOT NULL,
	[StockCode]             NVARCHAR (8)    NOT NULL,
	

)

CREATE TABLE investment.SymbolData
(
	[Symbol]				NVARCHAR (8)    NOT NULL,
	[CompanyName]           NVARCHAR (128)  NOT NULL,
	[ExchangeName]          NVARCHAR (128)  NOT NULL,
	[Datasource]			NVARCHAR (128)  NOT NULL,
	[DataUrl]				NVARCHAR(2048)	NOT NULL,
	[UpdatedDateTime]		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
);

select top 5 * from investment.SymbolData;

INSERT INTO investment.SymbolData (Symbol, CompanyName, ExchangeName, Datasource, DataUrl, UpdatedDateTime) VALUES (Symbol, CompanyName, ExchangeName, Datasource, DataUrl, UpdatedDateTime);

select * from investment.Stock

select * from investment.SymbolData where CompanyName LIKE 'Apple%';
SELECT s.Name, pc.* from [investment].[Purchase] pc
INNER JOIN investment.Stock s
ON pc.MIC = s.MIC AND pc.Code = s.Code
